/*
ndff-datamodel3-functions
Database objects (functions) to enhance the usability of the NDFF database, datamodel 3
*/
begin;
SET client_min_messages = WARNING;
drop schema if exists fn cascade;
create schema fn;
grant usage on schema fn to public;
COMMENT ON SCHEMA fn IS 'Schema voor toegevoegde objecten om het gebruik van het Basisarchief met SQL aangenamer, sneller en/of van hogere kwaliteit te maken.';

/*
  ID<-->URI functies
  Omrekenen van id's in URI's en andersom
*/
create or replace function fn.uri2cde_id(p_cde_identity text) returns bigint as $$
select cde_id
from dictionary.codes
where identity = $1;
$$ language sql strict immutable;
comment on function fn.uri2cde_id(p_cde_identity text) is E'Geeft het id van een code(uri). Usage:
select fn.uri2cde_id(\'http://ndff-ecogrid.nl/codes/usageconditions/blurred\')';

create or replace function fn.cde_id2uri(p_cde_id bigint) returns text as $$
select identity
from dictionary.codes
where cde_id = $1;
$$ language sql strict immutable;
comment on function fn.uri2cde_id(p_cde_identity text) is E'Geeft de identity van een code(id). Usage:
select fn.cde_id2uri(179705040)';

/*
  REPRESENT
  Geeft de naam van een code weer in een gewenste taal
  code input kan een id of een uri zijn
*/

create or replace function fn.represent(p_cde_id bigint, p_lge_id bigint) returns text as $$
declare
t_displayname text; --het resultaat
begin
t_displayname := (select displayname
	from dictionary.representations
	where cde_id = p_cde_id
	and lge_id = p_lge_id
	and isprimary
	limit 1); --dit laatste vanwege mantis #4895, er zijn daar meerdere primary van dezelfde cde_id+lge_id
IF t_displayname ISNULL THEN 
		--misschien gaat het om een taxon. dan de wetenschappelijke naam direct uit de taxa tabel halen
		t_displayname := (select trim(' ' from (cde.name || ' ' || txa.specificepithet || ' ' || coalesce(txa.infraspecificepithet,''))) as taxon
			from dictionary.taxa txa
			join dictionary.codes cde on (txa.cde_id = cde.cde_id)
			where txa.cde_id = p_cde_id);
			IF t_displayname ISNULL THEN --blijkbaar geen taxon, dan maar gewoon de naam van de code
				t_displayname := (select "name" from dictionary.codes where cde_id = p_cde_id);
			--else (t_displayname NOTNULL) doorgaan, naar return 
			END IF;
--else (t_displayname NOTNULL) doorgaan, naar return 
END IF;

return t_displayname;
end
$$ language plpgsql strict stable;
comment on function fn.represent(p_cde_id bigint, p_lge_id bigint) is E'geeft de naam van een code(id) in de gevraagde taal(id). Usage:
select fn.represent( 2197851, 1);
voor soortnamen of andere codes te gebruiken
als in de gevraagde taal geen representation bestaat, wordt er gehandeld afhankelijk van het soort code of eigenlijk wat er gevonden kan worden:
	- voor en taxon (p_cde_id staat in dictionary.taxa) wordt de wetenschappelijke naam uit de tabellen codes en taxa weergegeven
	- voor andere codes wordt gewoon het veld "name" uit codes weergegeven
deze functie dient ook als basis voor meerdere wrappers.';

create or replace function fn.represent(p_cde_id bigint, p_lge_identity text) returns text as $$
select fn.represent($1, lge_id)
from dictionary.languages
where identity = $2;
$$ language sql strict stable;
comment on function fn.represent(bigint, text) is E'geeft de naam van een code(id) in de gevraagde taal(uri). Usage:
select fn.represent( 2197851, \'http://ndff-ecogrid.nl/codes/languages/nl-NL\');
voor soortnamen of andere codes te gebruiken
als in de gevraagde taal geen representation bestaat, wordt er gehandeld afhankelijk van het soort code of eigenlijk wat er gevonden kan worden:
	- voor en taxon (p_cde_id staat in dictionary.taxa) wordt de wetenschappelijke naam uit de tabellen codes en taxa weergegeven
	- voor andere codes wordt gewoon het veld "name" uit codes weergegeven';

create or replace function fn.represent(p_cde_identity text, p_lge_identity text) returns text as $$
select fn.represent(fn.uri2cde_id($1), lgn.lge_id)
from dictionary.languages lgn
where lgn.identity = $2
$$ language sql strict stable;
comment on function fn.represent(text, text) is E'geeft de naam van een code(uri) in de gevraagde taal(uri). Usage:
select fn.represent( \'http://ndff-ecogrid.nl/taxonomy/taxa/lemnaminor\', \'http://ndff-ecogrid.nl/codes/languages/nl-NL\');
voor soortnamen of andere codes te gebruiken
als in de gevraagde taal geen representation bestaat, wordt er gehandeld afhankelijk van het soort code of eigenlijk wat er gevonden kan worden:
	- voor en taxon (p_cde_id staat in dictionary.taxa) wordt de wetenschappelijke naam uit de tabellen codes en taxa weergegeven
	- voor andere codes wordt gewoon het veld "name" uit codes weergegeven';

create or replace function fn.nl(bigint) returns text as $$
select fn.represent($1, 1);
$$ language sql strict stable; 
comment on function fn.nl(bigint) is 'Geeft de Nederlands naam van een code(id). Usage:
select fn.nl(2197851);';
--een taal is geen code. evt kan de hard gecodeerde lge_id worden vervangen door een immutable uri2lge_id functie met een uri, net als de join in represent(text, text)

create or replace function fn.sci(bigint) returns text as $$
select fn.represent($1, 101);
$$ language sql strict stable;
comment on function fn.sci(bigint) is 'Geeft de wetenschappelijke naam van een code(id). Usage:
select fn.sci(2197851);';

create or replace function fn.sci_author(bigint) returns text as $$
select fn.represent($1, 151);
$$ language sql strict stable;
comment on  function fn.sci_author(bigint) is 'Geeft de wetenschappelijke naam van een code(id) inclusief literatuur referentie usage:
select fn.sci_author(2197851);';


/*
  INVOLVEMENT
  De rol van een persoon bij een waarneming of dataset
*/


CREATE OR REPLACE FUNCTION fn.involvement_svy(p_dst_id bigint, p_involvement_type_uri text) RETURNS TEXT AS $$
DECLARE
t_ite_cde_id bigint; --de internde code id van de p_involvement_type_uri
t_delimiter text := ';'; --het scheidingsteken tussen evt meerdere uri's
t_person_identities text; --het resultaat: een uri of delimiteld rijtje uri's van de betrokken personen op het laagste niveau. Mag initieel null zijn, er wordt bij(vooraf) het eerste resultaat een lege string in geplaatst. Dit, zodat de functie NULL teruggeeft als er geen resultaat is.
t_rec record; --record type voor meerdere iteratieve processen
--t_survey survey.surveys; --record van het type survey.surveys
t_numresults integer := 0; --het aantal resultaten, bijv om het eerste resultaat zonder delimiter te kunnen returnen. Moet initieel 0 zijn zodat + lukt.
t_dst_id bigint := p_dst_id; --dataset id in verschillende iteraties.
BEGIN
t_ite_cde_id := fn.uri2cde_id(p_involvement_type_uri);
IF (t_ite_cde_id ISNULL) THEN
	RAISE EXCEPTION E'involvement type \'%\' bestaat niet',p_involvement_type_uri;
END IF;
<<surveys>>
LOOP
	<<surveyinvolvements>>
	FOR t_rec IN (select person_identity from survey.survey_involvements where svy_id = t_dst_id and ite_cde_id = t_ite_cde_id)
	LOOP
		IF (t_numresults > 0) THEN
			t_person_identities := t_person_identities||t_delimiter;
		ELSE
			t_person_identities := ''; --maakt t_person_identities notnull, zodat concat met || lukt.
		END IF;
		t_person_identities := t_person_identities||t_rec.person_identity; --coalesce(person_identity, t_nullstring) is niet nodig, omdat het veld person_identity een NOT NULL constraint heeft.
		t_numresults := t_numresults+1;
	END LOOP surveyinvolvements;
	t_dst_id := (select parent_dst_id from observation.datasets dst where dst_id = t_dst_id); --weggelaten (mogelijk onterechte iteraties, maar die wegen niet op): join survey.surveys svy on svy.dst_id = dst.dst_id 
	EXIT WHEN t_dst_id ISNULL OR t_numresults > 0;	
END LOOP surveys;
RETURN t_person_identities;	
END
$$ LANGUAGE plpgsql STRICT STABLE;
comment on function fn.involvement_svy(p_dst_id bigint, p_involvement_type_uri text) is E'geeft de persoons-uri\'s van de personen met de gevraagde rol bij een survey (uitgebreide dataset)
wordt gebruikt in involvement_osn, omdat de rol van een persoon voor een waarneming ook in een map gedefinieerd kan zijn, zelfs hogerop in de hierarchische boom.
deze functie houdt rekening met die hierarchie. er wordt eerst gekeken of er een involvement is voor deze map zelf, dan naar evt surveys hoger in de boom(parents). De eerste gevonden set involvements is de juiste (lager niveau overrulet hoger niveau).
er kunnen meerdere personen dezelfde involvement hebben op hetzelfde niveau. In dat geval worden die allemaal gegeven, gescheiden door een scheidingsteken (nader te specificeren in declare).
De splitsing van deze functie en involvement_osn heeft een performance voordeel doordat er meerder waarnemingen in een dataset zitten, zodat deze functie meer dan eens in dezelfde query met dezelfde argumenten aangeroepen wordt. Het antwoord komt in dat geval uit de cache vanwege de volatility classification STABLE.
usage: 
select fn.involvement_svy(6347516, \'http://ndff-ecogrid.nl/codes/involvementtypes/data_owner\');
indirect:
select fn.involvement_osn(1008520015, \'http://ndff-ecogrid.nl/codes/involvementtypes/observer\');
select fn.owner(2362207513);
';

CREATE OR REPLACE FUNCTION fn.involvement_osn(p_osn_id bigint, p_involvement_type_uri text) returns text as $$
DECLARE
t_ite_cde_id bigint; --de internde code id van de p_involvement_type_uri
t_delimiter text := ';'; --het scheidingsteken tussen evt meerdere uri's
t_person_identities text; --het resultaat: een uri of delimiteld rijtje uri's van de betrokken personen op het laagste niveau. Mag initieel null zijn, er wordt bij(vooraf) het eerste resultaat een lege string in geplaatst. Dit, zodat de functie NULL teruggeeft als er geen resultaat is.
t_rec record; --record type voor meerdere iteratieve processen
t_survey survey.surveys; --record van het type survey.surveys
t_numresults integer := 0; --het aantal resultaten, bijv om het eerste resultaat zonder delimiter te kunnen returnen. Moet initieel 0 zijn zodat + lukt.
t_dst_id bigint; --dataset id in verschillende iteraties.
BEGIN
t_ite_cde_id := fn.uri2cde_id(p_involvement_type_uri);
IF (t_ite_cde_id ISNULL) THEN
	RAISE EXCEPTION E'involvement type \'%\' bestaat niet',p_involvement_type_uri;
END IF;

--eerste waarden ophalen, kunnen meerdere zijn.
<<osn>>
FOR t_rec IN (select person_identity from observation.observation_involvements where osn_id = p_osn_id and ite_cde_id = t_ite_cde_id)
LOOP
	IF (t_numresults > 0) THEN
		t_person_identities := t_person_identities||t_delimiter;
	ELSE
		t_person_identities := '';--maakt t_person_identities notnull, zodat concat met || lukt.
	END IF;
	t_person_identities := t_person_identities||t_rec.person_identity; --coalesce(person_identity, t_nullstring) is niet nodig, omdat het veld person_identity een NOT NULL constraint heeft.
	t_numresults := t_numresults+1;
END LOOP osn;
IF (t_numresults > 0) THEN
	RETURN t_person_identities; --dit is in dat geval het einde
END IF;

t_dst_id := (select dst_id from observation.observations where osn_id = p_osn_id); --heeft een NOT NULL constraint
RETURN (SELECT fn.involvement_svy(t_dst_id ,p_involvement_type_uri));
END
$$ LANGUAGE plpgsql STRICT STABLE;
comment on function fn.involvement_osn(p_osn_id bigint, p_involvement_type_uri text) is E'geeft de persoons-uri\'s van de personen met de gevraagde rol bij een waarneming
wordt gebruikt in wrapper functies die meteen de owner of waarnemer ophalen.
er wordt eerst gekeken of er een involvement is voor de waarneming zelf, dan naar evt surveys. De eerste gevonden set involvements is de juiste (lager niveau overrulet hoger niveau).
er kunnen meerdere personen dezelfde involvement hebben op hetzelfde niveau. In dat geval worden die allemaal gegeven, gescheiden door een scheidingsteken (nader te specificeren in declare).
De splitsing van deze functie en involvement_svy heeft een performance voordeel doordat er meerder waarnemingen in een dataset zitten, zodat de functie involvement_svy meer dan eens in dezelfde query met dezelfde argumenten aangeroepen wordt. Het antwoord komt in dat geval uit de cache vanwege de volatility classification STABLE.
usage: 
select fn.involvement_osn(2362207513, \'http://ndff-ecogrid.nl/codes/involvementtypes/data_owner\');
select fn.involvement_osn(1008520015, \'http://ndff-ecogrid.nl/codes/involvementtypes/observer\');
select fn.owner(2362207513);
';

create or replace function fn.owner(p_osn_id bigint) returns text as $$
--usage: select fn.owner(1008520015); --deze geeft toevallig NULL terug, in afwachting van een beter voorbeeld.
select fn.involvement_osn($1, 'http://ndff-ecogrid.nl/codes/involvementtypes/data_owner');
$$ language sql strict stable;

create or replace function fn.observer(p_osn_id bigint) returns text as $$
--usage: select fn.observer(1008520015); --deze geeft toevallig NULL terug, in afwachting van een beter voorbeeld.
select fn.involvement_osn($1, 'http://ndff-ecogrid.nl/codes/involvementtypes/observer');
$$ language sql strict stable;


create or replace function fn.maintainer(p_osn_id bigint) returns text as $$
--usage: select fn.maintainer(1008520015); --deze geeft toevallig NULL terug, in afwachting van een beter voorbeeld.
select fn.involvement_osn($1, 'http://ndff-ecogrid.nl/codes/involvementtypes/maintainer_data');
$$ language sql strict stable;

/*
  TAGS
  soortgroepen, beleidsstatussen, etc.
*/
create or replace function fn.categories(p_root_catagory_cde_id bigint, p_cde_id bigint) returns text as $$
declare
t_result text := ''; --the result of the function
t_rec record;
t_len integer; --de lengte van het resultaat, zodat het niet 2 keer hoeft te worden opgevraagd (misschien is het lang...)
begin

for t_rec in
	with recursive 
	hoog as
		(select *
		from traits.categories c
		where cde_id = p_root_catagory_cde_id --(select cde_id from dictionary.codes where identity = 'http://ndff-ecogrid.nl/categories/policy')
	union all
		select c.*
		from traits.categories c
		join hoog on (c.parent_cde_id = hoog.cde_id ))

	select h.cde_id,fn.nl(h.cde_id) as cat
	from hoog h
	join traits.tags t on (h.cde_id = t.cte_cde_id)
	where t.cde_id = p_cde_id  --fn.uri2cde_id('http://ndff-ecogrid.nl/taxonomy/taxa/haliaeetusalbicilla')
	limit 100
LOOP
		t_result := t_result||coalesce(t_rec.cat, '<NULL>')||';'; --semicolon is the delimiter
END LOOP;
t_len := length(t_result);
IF t_len > 0 THEN
	t_result := substr(t_result, 1, t_len-1); --remove semicolon at the end
END IF;
RETURN t_result;
END;
$$ language plpgsql strict stable;
comment on function fn.categories(bigint, bigint) is 'geeft, puntkomma gescheiden, de categorieen die op de opgegeven code van toepassing zijn, binnen de opgegevn hoofdcategorie. usage: (dit commentaar bevat meerdere regels..)
 select fn.categories(3586601,1888701)';

create or replace function fn.categories(p_root_catagory_uri text, p_cde_uri text) returns text as $$
select fn.categories(fn.uri2cde_id($1), fn.uri2cde_id($2));
$$ language sql strict stable;
comment on function fn.categories(text, text) is E'geeft, puntkomma gescheiden, de categorieen die op de opgegeven code van toepassing zijn, binnen de opgegevn hoofdcategorie. usage: (dit commentaar bevat meerdere regels..)
select fn.categories(\'http://ndff-ecogrid.nl/categories/speciesindex\',\'http://ndff-ecogrid.nl/taxonomy/taxa/haliaeetusalbicilla\')';

create or replace function fn.categories(p_root_catagory_uri text, p_cde_id bigint) returns text as $$
select fn.categories(fn.uri2cde_id($1), $2);
$$ language sql strict stable;
comment on function fn.categories(text, bigint) is E'geeft, puntkomma gescheiden, de categorieen die op de opgegeven code van toepassing zijn, binnen de opgegevn hoofdcategorie. usage: (dit commentaar bevat meerdere regels..)
select fn.categories(\'http://ndff-ecogrid.nl/categories/speciesindex\',1888701)';

create or replace function fn.policy_names(p_txn_id bigint) returns text as $$
 select fn.categories('http://ndff-ecogrid.nl/categories/policy', $1);
$$ language sql stable strict;
comment on function fn.policy_names(p_txn_id bigint) is E'geeft, puntkomma gescheiden, de beschermings statussen die op dit taxon van toepassing zijn. usage:
select fn.policy_names(1888701)';

create or replace function fn.policy_names(p_taxon_uri text) returns text as $$
	select fn.categories('http://ndff-ecogrid.nl/categories/policy', fn.uri2cde_id($1));
$$ LANGUAGE sql stable strict;
comment on function fn.policy_names(p_txn_id bigint) is E'geeft, puntkomma gescheiden, de beschermings statussen die op dit taxon van toepassing zijn. usage:
select fn.policy_names(\'http://ndff-ecogrid.nl/taxonomy/taxa/haliaeetusalbicilla\')';


/*
  FLEXIBLE FIELDS
  Het ophalen van data uit flexibele velden 
*/
create or replace function fn.nominalvalue(p_osn_id bigint, p_field_identity text) returns text as $$
select cde.name
from dictionary.codes cde --de code die weergegeven moet worden
join observation.observation_extra_info oei on (oei.nominalvalue_cde_id = cde.cde_id ) --de extra info/waarneming koppeling
where oei.osn_id = $1 --de observatie id (p_osn_iod) uit de input
and oei.key_cde_id = fn.uri2cde_id($2) --het veld waarnaar gezocht wordt
$$ language sql strict stable;
comment on function fn.nominalvalue(p_osn_id bigint, p_field_identity text) is E'geeft de nominale waarde van een veld bij een waarneming. usage:
select fn.nominalvalue(1405036, \'http://ndff-ecogrid.nl/codes/keys/observation/sexes\') as sexe';


/*
  HIERARCHICAL OBSERVATION PROPERTIES

*/
create or replace function fn.protocolid(p_osn_id bigint) returns bigint as $$
declare
t_survey survey.surveys; --record van tabel survey.surveys 
t_dst_id bigint ;--dynamisch dataset id (voor tabel surveys)
t_counter integer := 0; --counter die een oneindige loop voorkomt, moet initieel gevuld zijn zodat + lukt
begin
--eerste map ophalen
select * into t_survey 
	from survey.surveys s
	join observation.observations osn on (s.dst_id = osn.dst_id)
	where osn_id = p_osn_id;
<<parent>>
LOOP 
	t_counter := t_counter + 1;
	EXIT WHEN t_survey.ptl_cde_id NOTNULL OR t_counter >= 1000;
	--parent ophalen, hoewel op het moment van schrijven alle surveys een protocol hebben, dus zou deze parent-loop onnodig zijn en nooit gebruikt worden
	select s.* into t_survey 
	from survey.surveys s
	join observation.datasets dst on s.dst_id = dst.parent_dst_id
	where dst.dst_id = t_survey.dst_id;
END LOOP parent;
return t_survey.ptl_cde_id;
end;
$$ language plpgsql strict stable;  
comment on function fn.protocolid(p_osn_id bigint) is 'geeft het cde_id van het protocol voor deze waarneming. daarbij wordt rekening gehouden met de hierarchie van het datamodel. Usage:
select fn.protocolid(2021462507);';

CREATE OR REPLACE FUNCTION fn.protocolname(p_osn_id bigint)
  RETURNS text AS
$BODY$
select fn.nl(fn.protocolid($1));
$BODY$
  LANGUAGE 'sql' STABLE STRICT;
comment on FUNCTION fn.protocolname(p_osn_id bigint) is 'Geeft de naam van het protocol voor deze waarneming. daarbij wordt rekening gehouden met de hierarchie van het datamodel. Usage:
select fn.protocolname(1177507527);';

create or replace function fn.protocoluri(p_osn_id bigint) returns text as $$
select fn.cde_id2uri(fn.protocolid($1));
$$ language sql strict stable;  
comment on function fn.protocoluri(p_osn_id bigint) is 'geeft de uri van het protocol voor deze waarneming. daarbij wordt rekening gehouden met de hierarchie van het datamodel. Usage:
select fn.protocoluri(1177507527);';

create or replace function fn.dataset_root_name(p_dst_id bigint) returns text as $$
declare
t_dst_id bigint ;--dynamisch dataset id (voor tabel surveys)
t_counter integer := 0; --counter die een oneindige loop voorkomt, moet initieel gevuld zijn zodat + lukt
t_dataset observation.datasets; --record van tabel observation.datasets
t_dst_name text; --het resultaat, de naam van de dataset
begin

--vullen met eerste dataset, zoals input
select * into t_dataset
from observation.datasets
where dst_id = p_dst_id;

<<loopje>>
LOOP
	t_dst_name := t_dataset.name;
	t_counter := t_counter + 1;
	EXIT WHEN t_counter >= 1000 OR t_dataset.parent_dst_id ISNULL; --t_dataset.parent_dst_id ISNULL als het de bovenste map is, dus dan idd exit
	--parent ophalen
	select * into t_dataset
	from observation.datasets
	where dst_id = t_dataset.parent_dst_id;
END LOOP loopje;

return t_dst_name;
end
$$ language plpgsql strict stable;
comment on function fn.dataset_root_name(p_dst_id bigint) is 'Geeft de hoogste dataset in de hierarchie die de gevraagde dataset bevat (root). De input kan bijvoorbeeld de dataset zijn uit het veld dst_id van observations, dan krijg je de root dataset van de warneming.
Vaak is de hoogste dataset (root) het project in welk kader de waarnemingen verzameld zijn.
usage: select fn.dataset_root_name(452517)';


create or replace function fn.hascategory(p_txn_id bigint, p_cte_cde_id bigint) returns boolean as $$
SELECT EXISTS
	(with recursive 
	hoog as
		(select *
		from traits.categories c
		where cde_id = $2 --<== p_cte_cde_id is een input parameter (de categorie)
	union all
		select c.*
		from traits.categories c
		join hoog on (c.parent_cde_id = hoog.cde_id )) --<==join met zichzelf

	select h.cde_id,fn.nl(h.cde_id) as cat
	from hoog h
	join traits.tags t on (h.cde_id = t.cte_cde_id)
	where t.cde_id = $1) --<== p_txn_id is een input parameter (de soort)
	--limit voor het voorkomen van een oneindige loop is hier niet nodig, vanwege exists in de buitenste query
;
$$ language sql stable strict;
comment on function fn.hascategory(bigint, bigint) is 'Geeft aan of de code in de categorie zit ja/nee, of de in categorieen daaronder. Bijvoorbeeld of een bepaalde soort beschermd is, of tot een soortgroep behoort. Usage: 
	select fn.hascategory(1888701, 3587351) --is de Zeearend beschermd?
	select fn.hascategory(2172051, 3587351) --is de Paardebloem beschermd?';

create or replace function fn.hascategory(p_txn_id bigint,p_category_uri text) returns boolean as $$
	select fn.hascategory($1, fn.uri2cde_id($2));
$$ language sql stable strict;
comment on function fn.hascategory(bigint, text) is E'Geeft aan of de code in de categorie zit ja/nee, of de in categorieen daaronder. Bijvoorbeeld of een bepaalde soort beschermd is, of tot een soortgroep behoort. Usage: 
	select fn.hascategory(1888701, \'http://ndff-ecogrid.nl/categories/policy\') --is de Zeearend beschermd?
	select fn.hascategory(2172051, \'http://ndff-ecogrid.nl/categories/policy\') --is de Paardebloem beschermd?';

create or replace function fn.hascategory(p_taxon_uri text,p_category_uri text) returns boolean as $$
	select fn.hascategory(fn.uri2cde_id($1), fn.uri2cde_id($2));
$$ language sql stable strict;
comment on function fn.hascategory(text, text) is E'Geeft aan of de code in de categorie zit ja/nee, of de in categorieen daaronder. Bijvoorbeeld of een bepaalde soort beschermd is, of tot een soortgroep behoort. Usage: 
	select fn.hascategory(\'http://ndff-ecogrid.nl/taxonomy/taxa/haliaeetusalbicilla\', \'http://ndff-ecogrid.nl/categories/policy\') --is de Zeearend beschermd?
	select fn.hascategory(\'http://ndff-ecogrid.nl/taxonomy/taxa/taraxacumofficinale\', \'http://ndff-ecogrid.nl/categories/policy\') --is de Paardebloem beschermd?';

/*
   UTILS
   Diverse handige dingen
*/
CREATE OR REPLACE FUNCTION fn.squarefromcoordinates(IN x integer, IN y integer, IN size integer, IN SRID integer, OUT the_geom geometry) RETURNS geometry AS $$
       SELECT geomfromewkt('SRID='||$4||';POLYGON((' ||
               $1 || ' ' || $2 || ',' ||
               $1 || ' ' || $2+$3 || ',' ||
               $1+$3 || ' ' || $2+$3 || ',' ||
               $1+$3 || ' ' || $2 || ',' ||
               $1 ||' '|| $2 ||'))')
$$ LANGUAGE 'sql' IMMUTABLE STRICT;
COMMENT ON FUNCTION fn.squarefromcoordinates(integer, integer, integer, integer) IS 'Maakt een hok-geometrie uit coordinaten (passend bij de SRID) en grootte (zijde in eenheid passend bij de SRID) in het gevraagde coordinaten stelsel (SRID). Usage:
select fn.squarefromcoordinates(100000,400000,1000,28992)';

CREATE OR REPLACE FUNCTION fn.hokfromcoordinates(integer, integer, integer) RETURNS geometry AS $$
  SELECT fn.squarefromcoordinates($1,$2,$3,28992);
$$ LANGUAGE 'sql' IMMUTABLE STRICT;
COMMENT ON FUNCTION fn.hokfromcoordinates(integer, integer, integer) IS 'Maakt een hok-geometrie uit coordinaten (RD in meters) en grootte (zijde in meters). Usage:
select fn.hokfromcoordinates(100000,400000,1000)';


/*
  FLATTENDED CODE HIERARCHIES
  lookup tabellen voor correcte uri's, correcte namen, children, etc.
  
*/
create table fn.taxa_correct_loops as
select txn1.cde_id, txn1.correct_cde_id
from dictionary.taxa txn1
join dictionary.taxa txn2 on txn1.correct_cde_id=txn2.cde_id and txn2.correct_cde_id=txn1.cde_id;
comment on table fn.taxa_correct_loops is 'Deze tabel bevat cde_ids van taxa, die naar elkaar verwijzen met de correct_cde_id. Door zulke kringverwijzingen kunnen oneindige loops ontstaan. Hier worden alleen records gevonden, die diect naar elkaar verwijzen, dus niet via een3e record. Als het goed is, is de tabel leeg - als hij niet leeg is, moet er actie ondernomen worden om de kringwverwijzing te verwijderen.';

create table fn.taxa_correct as 
with recursive
correctable as
 (select cde.identity as identity, txn.cde_id as cde_id,
		case when tcl.cde_id > tcl.correct_cde_id then NULL --op deze manier blijven de soorten die naar elkaar verwijzen, behouden inlcusief 1 van de 2 correct verwijzingen.
		     else txn.correct_cde_id 
		     end as correct_cde_id, true as corrected
	from dictionary.taxa txn
	join dictionary.codes cde on txn.cde_id = cde.cde_id
	left join fn.taxa_correct_loops tcl on tcl.cde_id=txn.cde_id
	where txn.correct_cde_id notnull
union all
	select c.identity, c.cde_id,txn2.correct_cde_id,true as corrected
	--Voor andere doeleinden wil je mss liever tax2.cde_id gebruiken, maar hier zijn al alle niet-correcte taxa, dus het is niet nodig (zelfs fout) om nog weer stapjes omhoog te doen in de boom. Later (in de CTE "foo") worden alleen de correct_id's eruit gehaald die helemaal correct zijn, door een join met dictionary.taxa where correct_cde_id isnull. We willen elke niet-correcte cde_id vertalen naar de uiteindelijke correct_cde_id, daarom is cde_id steeds dezelfde, en gaat correct_cde_id steeds een verder.
	from correctable c
	join dictionary.taxa txn2 on c.correct_cde_id = txn2.cde_id
	)
,correct as
(select cde.cde_id ,cde.identity as original_identity,cde.cde_id as correct_cde_id, cde.identity as identity,false as corrected--,fn.sci(cde.cde_id) as sci_name, fn.nl(cde.cde_id) as nl_name, fn.categories('http://ndff-ecogrid.nl/categories/speciesindex',cde.cde_id) as soortgroepen, fn.policy_names(cde.cde_id) as wet_beleid 
   from dictionary.codes cde
   join dictionary.taxa on taxa.cde_id = cde.cde_id
   where taxa.correct_cde_id isnull
	)
,foo as 
(select c.cde_id,c.identity as original_identity, cde.cde_id as correct_cde_id, cde.identity as identity,c.corrected 
from correctable c
join dictionary.taxa t on c.correct_cde_id = t.cde_id
join dictionary.codes cde on c.correct_cde_id = cde.cde_id
where t.correct_cde_id isnull
union all
select * from correct
	)
select cde_id,original_identity, correct_cde_id, identity, corrected,fn.sci(correct_cde_id) as sci_name, fn.nl(correct_cde_id) as nl_name, fn.categories('http://ndff-ecogrid.nl/categories/speciesindex',correct_cde_id) as soortgroepen
, fn.policy_names(correct_cde_id) as wet_beleid, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/ffw/1') as ffwtabel1, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/ffw/2l') as ffwtabel2, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/ffw/3b') as ffwtabel3, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/rodelijst') as rodelijst, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/hrl/2') as hrlbijlage2, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/hrl/4') as hrlbijlage4
from foo ;

--drop table fn.taxa_correct;
alter table fn.taxa_correct add primary key(cde_id);
alter table fn.taxa_correct add constraint original_identity_unique unique (original_identity);
grant select on fn.taxa_correct to public;
comment on table fn.taxa_correct is E'lookup tabel (join is sneller dan functies!) voor correcte uri\'s, correcte nl-namen en correcte wetenschappelijke namen. Gaat hier om de uiteindelijk correcte zaken. Bijv:
Soort A verwijst naar B, B --> C en men vraagt naar de correcte naam van A, dan krijgt men C, niet B (B is niet meer correct).';
comment on column fn.taxa_correct.cde_id is E'de interne code id, kan verschillen van correct_cde_id (niet naar buiten brengen, alleen uri\'s)';
comment on column fn.taxa_correct.correct_cde_id is E'de interne, correcte code id. (niet naar buiten brengen, alleen uri\'s)';
comment on column fn.taxa_correct.original_identity is E'De originele uri, kan verschillen van de gecorrigeerde uri. Komt overeen met cde_id';
comment on column fn.taxa_correct.identity is E'De correcte uri.';
comment on column fn.taxa_correct.sci_name is E'De correcte wetenschappelijke naam.';
comment on column fn.taxa_correct.nl_name is E'De correcte Nederlandse naam.';
comment on column fn.taxa_correct.corrected is E'Geeft aan of cde_id verschilt van corrected_cde_id en dus of cde_id en original_identity achterhaald zijn door andere versies.';

create table fn.taxa_loopback as 
select tc1.cde_id, tc2.cde_id as cde_id2
from fn.taxa_correct tc1
join fn.taxa_correct tc2 on (tc1.correct_cde_id = tc2.correct_cde_id);
create index taxa_loopback_cde_id on fn.taxa_loopback (cde_id);
grant select on fn.taxa_loopback to public;
comment on table fn.taxa_loopback is E'lookup tabel voor het verkrijgen van alle id\'s die via correct relaties verbonden is met het gevraagde taxon';
comment on column fn.taxa_loopback.cde_id is E'het id van de soort dat je hebt';
comment on column fn.taxa_loopback.cde_id2 is E'alle id\'s die via correct_id\'s aan cde_id verbonden zijn.';

create table fn.taxa_children (txn_cde_id bigint, child_cde_id bigint);
insert into fn.taxa_children
with recursive
taxr as
	(select cde_id as start_cde_id,cde_id
	from dictionary.taxa 
	--where cde_id = 1684301
union all
	select taxr.start_cde_id,taxa.cde_id
	from dictionary.taxa
	join taxr on (taxa.parent_cde_id = taxr.cde_id))
select * from taxr;
create index taxa_children_txn_cde_id_idx on fn.taxa_children(txn_cde_id);
grant select on fn.taxa_children to public;
comment on table fn.taxa_children is E'alle "children" van alle taxa, om bijvoorbeeld alle kranswieren op te halen (als die geen "categorie" zijn in traits). Usage:
--10 waarnemimngen van characeae:
select identity
from observation.observations o
join fn.taxa_children tc on (tc.child_cde_id = o.subject_txn_id and tc.txn_cde_id = fn.uri2cde_id(\'http://ndff-ecogrid.nl/taxonomy/taxa/characeae\'))
limit 10';

commit;
